﻿
CREATE FUNCTION [dbo].[NotEmptyDays]
(
	@DepartmentId int, -- код подразделения
	@PeriodBegin smalldatetime,
	@PeriodEnd smalldatetime,
	@WithAggregate bit = 0		-- =1- расчет с агрегированием товаров у которых одинаковые AssortmentId, RetailPack (точнее DefekturaAggregate = 1)
)
RETURNS @tblGoodsFlow TABLE (
	[GoodsId] [int] NOT NULL ,
	[DocDate] smalldatetime NOT NULL,
	[Turnover] real NULL,			--оборот за день
	[SaleQntty] real NULL,		--продажи за день
	[SaleSum] [decimal](19, 2) NULL,
	[DayRest] real NULL,			--остаток на конец дня
	[FullDays] [int] NULL			--количество дней присутствия
	PRIMARY KEY (GoodsId, DocDate)
)
AS
BEGIN
/* Черноуосов ОП 30.03.06 Вычисление дней присутствия */
DECLARE @intRet integer, @intErrCode int, @strAction varchar(50)
DECLARE @realTurnover real, @realSale real, @intGoodsId int
DECLARE @datDocDate smalldatetime, @decSaleSum [decimal](19, 2)
DECLARE @intPrevGoodsId int, @datPrevFullDate smalldatetime, @intFullDays int
DECLARE @realDayRest real, @ImportedRows int

DECLARE @tblBeginRest TABLE (
	[GoodsId] [int] NOT NULL ,
	[Turnover] real NULL,
	[SaleQntty] real NULL,
	[SaleSum] [decimal](19, 2) NULL 
	PRIMARY KEY (GoodsId)
)

DECLARE @tblAggregates TABLE (
	[GoodsId] [int] NOT NULL ,
	AggrGoodsId [int] NOT NULL
	PRIMARY KEY (GoodsId)
) 

DECLARE @tblDaySales TABLE (
	[GoodsId] [int] NOT NULL,
	DocDate smalldatetime NOT NULL,
	[Turnover] real NOT NULL,
	[SaleQntty] real NOT NULL,
	[SaleSum] [decimal](19, 2) NOT NULL 
)

DECLARE @tblToParentGoods TABLE ( -- для пересчета количества потомков в количество родителей
	[GoodsId] [int] NOT NULL,
	ParentGoodsId int NULL,
	ParentRetailPack int,
	ChildRetailPack int
	PRIMARY KEY (GoodsId)
)

INSERT INTO @tblToParentGoods (GoodsId)
SELECT DISTINCT GoodsId FROM CD.Parties

UPDATE D SET 
ParentGoodsId = P.Id,
ChildRetailPack = C.RetailPack,
ParentRetailPack = ( CASE WHEN P.Id Is Null THEN C.RetailPack ELSE P.RetailPack END )
FROM @tblToParentGoods D INNER JOIN CA.Goods C ON D.GoodsId = C.Id
LEFT OUTER JOIN CA.Goods P ON C.Id = P.ChildId

SELECT @realDayRest = 0, @ImportedRows = 0, @intPrevGoodsId = 0

--select @PeriodBegin = '2005-11-01', @PeriodEnd = '2006-01-31'

--DocTypeId = 3 Кассовый чек
--определяем товары с остатком на начало периода
INSERT INTO @tblBeginRest (GoodsId, Turnover, SaleQntty, SaleSum)
SELECT GoodsId, SUM(Qntty) AS Turnover,
SUM(CASE DocTypeId WHEN 3 THEN -1.0 * Qntty ELSE 0.0 END) AS SaleQntty,
SUM(CASE DocTypeId WHEN 3 THEN -1.0 * RetailSum ELSE 0.0 END) AS SaleSum
FROM (
	SELECT IsNull(P.ParentGoodsId,P.GoodsId) AS GoodsId, F.DocTypeId, F.RetailSum,
--количество по разобранным товарам приводим к родителям	
	CASE WHEN P.ParentGoodsId Is Null THEN cast(F.Qntty as real) ELSE cast(F.Qntty as real) * cast(P.ChildRetailPack as real) / cast(P.ParentRetailPack as real) END AS Qntty
	FROM dbo.vwGoodsFlow F INNER JOIN @tblToParentGoods P ON F.GoodsId = P.GoodsId
	WHERE F.DocDate < @PeriodBegin
	AND (DepartmentId = @DepartmentId OR @DepartmentId IS NULL) 
) T
GROUP BY GoodsId
HAVING SUM(Qntty) > 0

--остатки и товародвижение без агрегирования
INSERT INTO @tblDaySales (GoodsId, DocDate, Turnover, SaleQntty, SaleSum)
SELECT GoodsId, dateadd(day, -1, @PeriodBegin ) AS DocDate, Turnover, SaleQntty, SaleSum FROM @tblBeginRest

INSERT INTO @tblDaySales (GoodsId, DocDate, Turnover, SaleQntty, SaleSum)
SELECT GoodsId, DocDate, SUM(Qntty) AS Turnover,
SUM(CASE DocTypeId WHEN 3 THEN -1.0 * Qntty ELSE 0.0 END) AS SaleQntty,
SUM(CASE DocTypeId WHEN 3 THEN -1.0 * RetailSum ELSE 0.0 END) AS SaleSum
FROM (
	SELECT IsNull(P.ParentGoodsId,P.GoodsId) AS GoodsId, F.DocTypeId, F.RetailSum, dbo.NormalizeDate(F.DocDate) AS DocDate,
--количество по разобранным товарам приводим к родителям	
	CASE WHEN P.ParentGoodsId Is Null THEN cast(F.Qntty as real) ELSE cast(F.Qntty as real) * cast(P.ChildRetailPack as real) / cast(P.ParentRetailPack as real) END AS Qntty
	FROM dbo.vwGoodsFlow F INNER JOIN @tblToParentGoods P ON F.GoodsId = P.GoodsId
	WHERE DocDate >= @PeriodBegin AND DocDate < dateadd(day, 1, @PeriodEnd)
	AND (DepartmentId = @DepartmentId OR @DepartmentId IS NULL) 
) T
GROUP BY GoodsId, DocDate


IF @WithAggregate = 1 
BEGIN
--вычисляем товары по которым будет происходить агрегированнный расчет
	INSERT INTO @tblAggregates (GoodsId, AggrGoodsId)
	SELECT G.Id AS GoodsId, A.AggrGoodsId
	FROM ( --изменено 23,11,06
		SELECT T.AssortmentId, T.RetailPack, max(T.Id) as AggrGoodsId --самый новый код товара
		FROM CA.Goods T INNER JOIN (
			SELECT GoodsId FROM CD.Parties --только те товары которые были в обороте
			UNION
			SELECT GoodsId FROM CD.InputLines -- и в приходе
		) D ON T.Id = D.GoodsId
		WHERE T.DefekturaAggregate = 1
		GROUP BY AssortmentId, RetailPack
	) A INNER JOIN CA.Goods G ON A.AssortmentId = G.AssortmentId AND A.RetailPack = G.RetailPack AND G.DefekturaAggregate = 1

	INSERT INTO @tblAggregates (GoodsId, AggrGoodsId)
	SELECT A.GoodsId, A.GoodsId FROM (
		SELECT DISTINCT GoodsId FROM CD.Parties WHERE GoodsId NOT IN (SELECT GoodsId FROM @tblAggregates)
	) As A

	DECLARE CrsrByDate CURSOR FAST_FORWARD FOR
	SELECT T.GoodsId, T.DocDate, sum(T.Turnover), sum(T.SaleQntty), sum(T.SaleSum)
	FROM (
		SELECT CASE WHEN G.AggrGoodsId IS NULL THEN S.GoodsId ELSE G.AggrGoodsId END AS GoodsId,
		S.DocDate, S.Turnover, S.SaleQntty, S.SaleSum
		FROM @tblDaySales S LEFT OUTER JOIN @tblAggregates G ON S.GoodsId = G.GoodsId
	) T
	GROUP BY T.GoodsId, T.DocDate ORDER BY T.GoodsId, T.DocDate

END ELSE BEGIN
	DECLARE CrsrByDate CURSOR FAST_FORWARD FOR
	SELECT GoodsId, DocDate, Turnover, SaleQntty, SaleSum FROM @tblDaySales
	ORDER BY GoodsId, DocDate
END

--расчитываем остатки на конец дня
OPEN CrsrByDate
FETCH NEXT FROM CrsrByDate INTO @intGoodsId, @datDocDate, @realTurnover, @realSale, @decSaleSum

WHILE (@@FETCH_STATUS = 0)
BEGIN

	IF @intPrevGoodsId = @intGoodsId 
	BEGIN
		SET @realDayRest = @realDayRest + @realTurnover
	END ELSE BEGIN
		SELECT @realDayRest = @realTurnover, @datPrevFullDate = NULL
	END

	IF @datPrevFullDate IS NULL 
	BEGIN
		IF @realSale > 0.0 SET @intFullDays = 1 --если были продажи значит товар присутствовал
		ELSE SET @intFullDays = 0
	END
	ELSE SET @intFullDays = datediff(day, @datPrevFullDate, @datDocDate)

	INSERT INTO @tblGoodsFlow( GoodsId, DocDate, Turnover, SaleQntty, SaleSum, DayRest, FullDays)
	VALUES (@intGoodsId, @datDocDate, @realTurnover, @realSale, @decSaleSum, @realDayRest, @intFullDays)

	IF @realDayRest = 0 SET @datPrevFullDate = NULL
	ELSE SET @datPrevFullDate = @datDocDate

	SELECT @ImportedRows = @ImportedRows + 1, @intPrevGoodsId = @intGoodsId 

	FETCH NEXT FROM CrsrByDate INTO @intGoodsId, @datDocDate, @realTurnover, @realSale, @decSaleSum

END

CLOSE CrsrByDate
DEALLOCATE CrsrByDate

--если на день последней операции в периоде остаток > 0 то увеличиваем кол-во дней присутствия до конца периода
UPDATE D SET D.FullDays = CASE IsNull(D.DayRest, 0.0) WHEN 0 THEN D.FullDays  ELSE D.FullDays  + datediff(day, D.DocDate, @PeriodEnd) END 
FROM @tblGoodsFlow D INNER JOIN (
	SELECT GoodsId, max(DocDate) AS DocDate FROM @tblGoodsFlow GROUP BY GoodsId
) T ON D.GoodsId = T.GoodsId AND D.DocDate = T.DocDate

	RETURN 
END
